--The following is the Generic SQL Query Template, which can be adapted to national institute's databases,
--to extract fish catch data and biological sample data and convert it into the InterCatch exchange format.
--
--Below is an example of a catch file (HI and SI record) with sample data (SD record) for one quarter,
--one country, one fleet/fisheries/metier, one area, one species in the InterCatch Exchange Format.
--
--HI,DK,2005,Quarter,1,Bottom trawl,Div,IIIa,NA,NA,-9,NA
--SI,DK,2005,Quarter,1,Bottom trawl,Div,IIIa,NA,anf,na,L,A,NA,NA,M,NA,T,1.494423,-9,-9,Gillnet,Maria,Second country
--SD,DK,2005,Quarter,1,Bottom trawl,Div,IIIa,NA,anf,na,L,A,N,Age,3,10,1483423,25,7852,25,846,g,N,year,cm,NA,235.000,821,22,-9,-9,-9
--SD,DK,2005,Quarter,1,Bottom trawl,Div,IIIa,NA,anf,na,L,A,N,Age,4,10,1483423,25,7852,25,846,g,N,year,cm,NA,775.000,1020,25,-9,-9,-9
--SD,DK,2005,Quarter,1,Bottom trawl,Div,IIIa,NA,anf,na,L,A,N,Age,5,10,1483423,25,7852,25,846,g,N,year,cm,NA,215.000,1410,28,-9,-9,-9
--SD,DK,2005,Quarter,1,Bottom trawl,Div,IIIa,NA,anf,na,L,A,N,Age,6,10,1483423,25,7852,25,846,g,N,year,cm,NA,66.000,2180,34,-9,-9,-9
--SD,DK,2005,Quarter,1,Bottom trawl,Div,IIIa,NA,anf,na,L,A,N,Age,7,10,1483423,25,7852,25,846,g,N,year,cm,NA,11.000,3220,37,-9,-9,-9

--Unlike the example above where all the data that belongs together (Country,quarter,1, etc.) are right after each other,
--data can also be grouped together after record type. This means all the HI-records can be grouped together 
--directly after each others, then followed by SI-records, and finally all the SD-records.
--
--The following Generic SQL Query Template group the record types together, InterCatch is independent of how data are grouped.
--The Generic SQL Query Template is SPLIT IN 2 QUERIES one for HI- and SI-recors, and another for SD-records

--FIRST QUERY FOR HI- and SI-recors

Select 'HI' as Record_Id
,CASE 
WHEN InternalCountry.Code = 'Norway' THEN 'NO'
WHEN InternalCountry.Code = 'England' THEN 'UKE'
WHEN InternalCountry.Code = 'Scotland' THEN 'UKS'
WHEN InternalCountry.Code = 'Germany' THEN 'DE'
WHEN InternalCountry.Code = 'Sweden' THEN 'SE'
ELSE 'Error No ref. found'
END Country
,Year
,CASE 
WHEN InternalSeasonType.Code = 'Yea' THEN 'Year'
WHEN InternalSeasonType.Code = 'Qua' THEN 'Quarter'
WHEN InternalSeasonType.Code = 'Mon' THEN 'Month'
ELSE 'Error No ref. found'
end SeasonType
,CASE 
WHEN InternalSeason.Code = '1' THEN '1'
WHEN InternalSeason.Code = '2' THEN '2'
WHEN InternalSeason.Code = '3' THEN '3'
WHEN InternalSeason.Code = '4' THEN '4'
ELSE 'Error No ref. found'
end Season
,CASE 
WHEN InternalFleet.Code = 'TRW' THEN 'Bottom Trawlers'
WHEN InternalFleet.Code = 'PUR' THEN 'Purseiners'
WHEN InternalFleet.Code = 'LONG' THEN 'Longliners'
ELSE 'Error No ref. found'
end Fleets
,CASE 
WHEN InternalAreaType.Code = 'Sub Area' THEN 'SubArea'
WHEN InternalAreaType.Code = 'Division' THEN 'Div'
WHEN InternalAreaType.Code = 'Sub division' THEN 'SubDiv'
WHEN InternalAreaType.Code = 'Statistical rectangles' THEN 'StatRec'
ELSE 'Error No ref. found'
end AreaType
,CASE 
WHEN InternalArea.Code = 'SD22' THEN 'BAL22'
WHEN InternalArea.Code = 'SD23' THEN 'BAL23'
WHEN InternalArea.Code = '4' THEN 'IV'
WHEN InternalArea.Code = '4a' THEN 'IVa'
WHEN InternalArea.Code = '3a' THEN 'IIIa'
ELSE 'Error No ref. found'
end Area
,'Add all the rest of the fields for the HI record','ReplaceThisWithALineBreakAftertheExtraction'
, 'SI' as Record_Id
,'Add all the fields from the HI record which is the same here',
,CASE 
WHEN InternalSpecies.Code = 'cod' THEN 'cod'
WHEN InternalSpecies.Code = 'anf' THEN 'ang'
WHEN InternalSpecies.Code = 'had' THEN 'had'
WHEN InternalSpecies.Code = 'ple' THEN 'ple'
WHEN InternalSpecies.Code = 'sol' THEN 'sol'
WHEN InternalSpecies.Code = 'mac' THEN 'mac'
ELSE 'Error No ref. found'
END species
, sto.code StockCode
,CATON
,'Add all the rest of the fields for the SI record'
FROM Internal_Table1
inner join Internal_Table2 on Internal_Table1.Internal_Table1id = Internal_Table2.Internal_Table1id
WHERE Internal_species.code = 'haddock'

--SECOND QUERY FOR SD-recors

Select 'SD' as Record_Id
,'Add all the rest of the fields for the SI record'
,InternalNumberLanded
,InternalMeanWeight
FROM Internal_Table1
inner join Internal_Table2 on Internal_Table1.Internal_Table1id = Internal_Table2.Internal_Table1id
WHERE Internal_species.code = 'haddock'